package com.kb.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.car.util.ConnectionPool;
import com.car.util.Util;
import com.kb.bean.KBOfBitAuto;

public class KBDaoOfBitAuto {
	private Connection conn = ConnectionPool.getConnection("bitauto", "root", "zhouzhou100428", "localhost");
	private Connection co = ConnectionPool.getConnection("carvoice_core", "root", "zhouzhou100428", "localhost");

	public List<String> getUrlSpell() {
		List<String> urlSpells = new ArrayList<>();
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT bitauto_urlspell AS urlSpell FROM core_brand_bitauto WHERE bitauto_urlspell NOT LIKE '%allroad'";
		try {
			st = co.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				String urlSpell = rs.getString("urlSpell");
				urlSpells.add(urlSpell);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return urlSpells;
	}

	public int urlSpellToID(String urlSpell) {
		int carid = 0;
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT bitauto_bbs FROM core_brand_bitauto WHERE bitauto_urlspell='" + urlSpell + "'";
		try {
			st = co.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				carid = rs.getInt("bitauto_bbs");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return carid;

	}

	// bitauto_ltgxsj bitauto_kbgxsj
	public void updateBBSTime(String type, String urlSpell) {
		PreparedStatement ps = null;
		String sql = "UPDATE core_brand_bitauto AS bit SET bit." + type + "=NOW() WHERE bit.bitauto_urlspell='"
				+ urlSpell + "'";
		try {
			ps = co.prepareStatement(sql);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public void insertKB(KBOfBitAuto kb) {
		PreparedStatement ps = null;
		String sql = "INSERT INTO bitauto_kb(kb_kbid,kb_carid,kb_cxid,kb_cxmc,kb_bt,kb_yhid,kb_plrq,kb_gmdd,kb_jxs,kb_kj,kb_dl,kb_ck,kb_syyh,kb_yh,kb_ssx,kb_wg,kb_ns,kb_xjb,kb_pz,kb_plnr,kb_plcd,kb_gmsj,kb_gmjg,kb_lc,kb_dzs,kb_djl,kb_hfs,kb_zhpf,kb_myfy,kb_zqsj,kb_sfjh,kb_yhdj,kb_tzs,kb_tzjh,kb_dq) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, kb.getKb_kbid());
			ps.setInt(2, kb.getKb_carid());
			ps.setString(3, kb.getKb_cxid());
			ps.setString(4, kb.getKb_cxmc());
			ps.setString(5, kb.getKb_bt());
			ps.setInt(6, kb.getKb_yhid());
			ps.setDate(7, (Date) Util.getDate(kb.getKb_plrq()));
			ps.setString(8, kb.getKb_gmdd());
			ps.setString(9, kb.getKb_jxs());
			ps.setDouble(10, kb.getKb_kj());
			ps.setDouble(11, kb.getKb_dl());
			ps.setDouble(12, kb.getKb_ck());
			ps.setDouble(13, kb.getKb_syyh());
			ps.setDouble(14, kb.getKb_yh());
			ps.setDouble(15, kb.getKb_ssx());
			ps.setDouble(16, kb.getKb_wg());
			ps.setDouble(17, kb.getKb_ns());
			ps.setDouble(18, kb.getKb_xjb());
			ps.setDouble(19, kb.getKb_pz());
			ps.setString(20, kb.getKb_plnr());
			ps.setInt(21, kb.getKb_plcd());
			// ps.setDate(22, (Date) Util.getDate(kb.getKb_gmsj()));
			ps.setDouble(22, kb.getKb_gmjg());
			ps.setInt(23, kb.getKb_lc());
			ps.setInt(24, kb.getKb_dzs());
			ps.setInt(25, kb.getKb_djl());
			ps.setInt(26, kb.getKb_hfs());
			ps.setInt(27, kb.getKb_zhpf());
			ps.setDouble(28, kb.getKb_myfy());
			ps.setDate(29, (Date) Util.getDate(kb.getKb_zqsj()));
			ps.setInt(30, kb.getKb_sfjh());
			ps.setString(31, kb.getKb_yhdj());
			ps.setInt(32, kb.getKb_tzs());
			ps.setInt(33, kb.getKb_tzjh());
			ps.setString(34, kb.getKb_dq());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public void insertError(String url) {
		PreparedStatement ps = null;
		String sql = "Insert into error_kb(url) values(?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, url);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		KBDaoOfBitAuto k = new KBDaoOfBitAuto();
		System.out.println(k.urlSpellToID("virage"));
	}
}
